Deleting

To delete an item:

The user must select the item or items that he wants to delete using the mouse or the keyboard
The user may press the delete key from the keyboard, or a delete button in the toolbar.
The program must ask for confirmation.
The program must build an SQL statement to perform the deletion (DELETE ... FROM ...).
The program executes the SQL statement (ExecuteNonQuery).
The program NOTIFIES the user of the results in case the items could not be deleted.
Update the list with the items.

Tip

When deleting items from a list view control, the primary key may be stored in the Data field of the control. To build the DELETE SQL statement you may use the primary key that is stored in the Data field of the control.

Problem 1

Create a project called DeleteItem to delete an item using a Wintempla Dialog Application with Toolbar Icons. To learn how to use the toolbar, see the section Wintempla > GUI > Toolbar

Step A

Drag a toolbar and set its name to toolbMain. Drag a list view control and set its name to lvItem.

Step B

Edit the DeleteItem.cpp file as shown. In this case, the toolbar will have only one button. When finish, compile and run the program.

DeleteItem.cpp

void DeleteItem::Window_Open(Win::Event& e)
{
     //________________________________________________________ toolbMain
     TBBUTTON tbButton[1];

     toolbMain.imageList.Create(20, 20, 1);
     toolbMain.imageList.AddIcon(this->hInstance, IDI_DELETE);

     toolbMain.SendMessage(TB_BUTTONSTRUCTSIZE, (WPARAM)(int)sizeof(TBBUTTON), 0);
     toolbMain.SetImageList(toolbMain.imageList);
     //_____________________________________
     tbButton[0].iBitmap=MAKELONG(0, 0);
     tbButton[0].idCommand=IDM_DELETE;
     tbButton[0].fsState=TBSTATE_ENABLED; // | TBSTATE_WRAP
     tbButton[0].fsStyle=BTNS_BUTTON;
     tbButton[0].dwData=0L;
     tbButton[0].iString= (LONG_PTR)L"Delete";

     toolbMain.SetBitmapSize(20, 20);
     toolbMain.SetButtonSize(24, 22);
     toolbMain.AddButtons(tbButton, 1);//includes the separator
     toolbMain.SendMessage(TB_AUTOSIZE, 0, 0);
     toolbMain.SetMaxTextRows(0);
     toolbMain.Show(SW_SHOWNORMAL);
     //________________________________________________ lvItem: Column Setup
     lvItem.Cols.Add(0, LVCFMT_LEFT, 200, L"Item name");
     lvItem.Cols.Add(1, LVCFMT_LEFT, 120, L"Model");
     lvItem.Cols.Add(2, LVCFMT_LEFT, 120, L"Brand");
     lvItem.Cols.Add(3, LVCFMT_LEFT, 120, L"Category");
}

Step C

Edit the DeleteItem.h file as shown.

DeleteItem.h

#pragma once //______________________________________ DeleteItem.h
#include "resource.h"

class DeleteItem: public Win::Dialog
{
public:
     DeleteItem()
     {
     }
     ~DeleteItem()
     {
     }
     void Delete();
     void FillListView();
protected:
     ...
};

Step D

Edit the DeleteItem.cpp file as shown.

DeleteItem.cpp

void DeleteItem::Window_Open(Win::Event& e)
{
... same code as before
}

void DeleteItem::Delete()
{
}

void DeleteItem::FillListView()
{
}

Step E

First, from the toolbox drag the SQL:SELECT list template and drop it in the FillListView function. Second, from the toolbox dray the SQL:DELETE template and drop it in the Delete function. Finally, edit the DeleteItem.cpp file as shown. Note that every time an item is deleted, the list view control is filled with the new items and the Delete button from the toolbar is disabled.

DeleteItem.cpp

void DeleteItem::Window_Open(Win::Event& e)
{
     ... same as before

     //____________________________________ Disable the Delete Button
     toolbMain.EnableButton(IDM_DELETE, false);

     FillListView(); // Fill the list view control with the items
}

void DeleteItem::Delete()
{
     //____________________________________________________________ 1. Get item_id
     LPARAM item_id;
     if (lvItem.GetSelectedData(item_id) == false) return;
     //_____________________________________________________________2. Ask for confirmation
     if (this->MessageBox(L"Are you sure you want to delete the selected item?", L"Delete Item",
          MB_YESNO | MB_ICONQUESTION) != IDYES) return;
     //____________________________________________________________ 3. Change cursor to Busy
     Win::HourGlassCursor hgc(true);
     //____________________________________________________________ 4. Create DELETE statement
     wstring cmd;
     Sys::Format(cmd, L"DELETE FROM item WHERE item_id=%d", item_id);
     //____________________________________________________________ 5. Execute DELETE
     Sql::SqlConnection conn;
     int rows = 0;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          rows = conn.ExecuteNonQuery(cmd);
          if (rows!=1)
          {
               this->MessageBox(Sys::Convert::ToString(rows), L"Error - deleted rows", MB_OK | MB_ICONERROR);
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     FillListView();
     toolbMain.EnableButton(IDM_DELETE, false);
}

void DeleteItem::FillListView()
{
     lvItem.SetRedraw(false); // stop redrawing the control when inserting items
     lvItem.Items.DeleteAll();
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item", 100, lvItem);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     lvItem.SetRedraw(true);
     toolbMain.EnableButton(IDM_DELETE, false);
}

Step F

Run the program to verify that everything is OK. Do not forget to edit the connection string or the ODBC information in the stdafx.h file.

Step G

We would like to enable the delete button only when something is selected in the list view control. Open Wintempla and double click the list view control. In the tab of events check the events in the figure below. We will use the ItemChanged event to enable or disable the Delete button. We will use the KeyDown event to delete an item using the key Delete from the keyboard. Edit the DeleteItem.cpp file as shown below.

DeleteItem.cpp

... same as before

void DeleteItem::lvItem_ItemChanged(Win::Event& e)
{
     if (lvItem.GetSelectedIndex() >= 0)
     {
          this->toolbMain.EnableButton(IDM_DELETE, true);
     }
     else
     {
          this->toolbMain.EnableButton(IDM_DELETE, false);
     }
}

void DeleteItem::lvItem_KeyDown(Win::Event& e)
{
     LPNMLVKEYDOWN p = (LPNMLVKEYDOWN) e.lParam;
     if (p->wVKey == VK_DELETE)
     {
          Delete();
     }
}

Step H

Test your program; you should be able to delete an item using the Delete key from the keyboard. The Delete key will work only when the list view control has the focus.

Step I

Open Wintempla and double click anywhere in the back of the interface. Go to the events tab and select Delete as shown below. After closing Wintempla, a function for the event will be generated. Call the function Delete() previously implemented.

DeleteItem.cpp

... same as before
void DeleteItem::Cmd_Delete(Win::Event& e)
{
Delete();
}

Problem 2

Modify the DeleteItem project so that the user can simultaneously delete multiple items.

Step A

Modify the DeleteItem.cpp file as shown. The function lvItem.GetSelectedCount() returns the number of items that are selected. First, the function lvItem.GetNextSelectedIndex(index) is called using an index of value of -1, the function returns the index of the first item that is selected. In order to get the index of the next selected item, the function is repeatedly called passing the index returned in the previous call.

DeleteItem.cpp

... same as before

void DeleteItem::Delete()
{
          //____________________________________________________________ 1. Count selected items
     const int selectedCount = lvItem.GetSelectedCount();
     if (selectedCount < 0) return;
     //_____________________________________________________________2. Ask for confirmation
     if (this->MessageBox(L"Are you sure you want to delete the selected item?", L"Delete Item",
          MB_YESNO | MB_ICONQUESTION) != IDYES) return;
     //____________________________________________________________ 3. Change cursor to Busy
     Win::HourGlassCursor hgc(true);
     Sql::SqlConnection conn;
     wstring cmd;
     int item_id = 0;
     int rows = 0;
     int index = -1;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          for(int i = 0; i < selectedCount; i++)
          {
               index = lvItem.GetNextSelectedIndex(index);
               if (index < 0) break;
               item_id = lvItem.Items[index].Data;
               //____________________________________________________________ 4. Create DELETE statement
               Sys::Format(cmd, L"DELETE FROM item WHERE item_id=%d", item_id);
               //____________________________________________________________ 5. Execute DELETE
               rows = conn.ExecuteNonQuery(cmd);
               if (rows!=1)
               {
                    this->MessageBox(Sys::Convert::ToString(rows), L"Error - deleted rows", MB_OK | MB_ICONERROR);
               }
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     FillListView();
}

void DeleteItem::lvItem_ItemChanged(Win::Event& e)
{
     if (lvItem.GetSelectedCount() >= 0)
     {
          this->toolbMain.EnableButton(IDM_DELETE, true);
     }
     else
     {
          this->toolbMain.EnableButton(IDM_DELETE, false);
     }
}

Tip

By default the list view control allows multiple items selection; you may use Wintempla and the properties tab to change the behavior of the control to single or multiple selections according to the requirements.

Problem 3

Create a Wintempla Web application called DeleteItemWeb to delete an item from the best_buy database. Publish the web application to a web server using Anonymous Access.

Step A

Add a list view control using the toolbar. Set the name to lvItem as shown below. Set the custom HTML as shown.

Step B

Add a button using the toolbar. Set the name to btDelete as shown below and add the onclick event as shown. First, unselect the “Submit” property of the button; second select the onclick event.

Step C

Edit the Index.h file as shown.

Index.h

#pragma once //_____________________________________________ Index.h
#include "resource.h"

class Index: public Web::Page
{
public:
     Index()
     {
     }
     ~Index()
     {
     }
     void FillListView();
private:
     ...
};

Step D

Edit the connection string or the ODBC information in the stdafx.h file.

Step E

Edit the Index.cpp file as shown.

Index.cpp

...
void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________ lvItem: Column Setup
     lvItem.Cols.Add(LVCFMT_LEFT, 30, L"Item name");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Model");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Brand");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Category");
     lvItem.Height = 30;
     FillListView();
}

void Index::btDelete_onclick(Web::HttpConnector& h)
{
     //____________________________________________________________ 1. Get item_id
     LPARAM item_id;
     if (lvItem.GetSelectedData(item_id) == false) return;
     //____________________________________________________________ 2. Create DELETE statement
     wstring cmd;
     Sys::Format(cmd, L"DELETE FROM item WHERE item_id=%d", item_id);
     //____________________________________________________________ 3. Execute DELETE
     Sql::SqlConnection conn;
     int rows = 0;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);

          rows = conn.ExecuteNonQuery(cmd);
          if (rows!=1)
          {
               this->MessageBox(Sys::Convert::ToString(rows), L"Error - deleted rows", MB_OK | MB_ICONERROR);
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     FillListView();
}

void Index::FillListView()
{
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);
          lvItem.Items.DeleteAll();
          conn.ExecuteSelect(L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item", 100, lvItem);
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Step F

Edit the Index.js file. Run and test the application.

Index.js

//_____________________________________________ Index.js
//window.onload=Window_onload();
//
//function Window_onload()
//{
//}

function btDelete_onclick(basicUrl, controlID, eventID)
{
     var answer = confirm("Press OK to delete the selected items");
     if (answer != true) return;
     SyncAll(basicUrl, controlID, eventID);
}

Problem 4

Modify the solution of the previous problem so that the user can delete several items simultaneously. Publish the web application to a web server using Anonymous Access. BE AWARE that in a Web application the Data field of each item in a list view control is a wstring variable.

Step A

Edit the Index.cpp file as shown.

Index.cpp

#include "stdafx.h" //_____________________________________________ Index.cpp
#include "Index.h"

void Index::Window_Open(Web::HttpConnector& h)
{
     ... same as before
     lvItem.MultipleSelection = true; //<<<<<< Add this line
}

void Index::btDelete_onclick(Web::HttpConnector& h)
{
     //_______________________________________________________________________ PROBLEM 4
     Sql::SqlConnection conn;
     wstring cmd;
     int rows = 0;
     int item_id = -1;
     const int itemCount = lvItem.Items.Count;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(NULL, CONNECTION_STRING);
          for (int i = 0; i < itemCount; i++)
          {
               if (lvItem.Items[i].Selected == false) continue;
               //____________________________________________________________ 1. Create DELETE statement
               Sys::Format(cmd, L"DELETE FROM item WHERE item_id=%s", lvItem.Items[i].Data.c_str());
               //____________________________________________________________ 2. Execute DELETE
               rows = conn.ExecuteNonQuery(cmd);
               if (rows != 1)
               {
                    this->MessageBox(Sys::Convert::ToString(rows), L"Error - deleted rows", MB_OK | MB_ICONERROR);
               }
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     FillListView();
}

void Index::FillListView()
{
     ... same as before
}

Problem 5

Create a program called DeleteItemS to delete one item using C# in a desktop application. Do not forget to store the item_id in the Tag property of each item of the list view control.

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DeleteItemS
{
     public partial class Form1 : Form
     {
          public Form1()
          {
               InitializeComponent();
          }

          private void lvItem_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
          {
               btDelete.Enabled = false;
               int count = lvItem.Items.Count;
               for (int i = 0; i < count; i++)
               {
                    if (lvItem.Items[i].Selected == true)
                    {
                         btDelete.Enabled = true;
                         break;
                    }
               }
               //btDelete.Enabled = false;
          }

          private void btDelete_Click(object sender, EventArgs e)
          {
               //________________________________________ Ask the user
               if (MessageBox.Show(this, "Are you sure?", "Delete Item",
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question,
                    MessageBoxDefaultButton.Button1) != DialogResult.Yes) return;

               //________________________________________ Build the SQL DELETE command
               int count = lvItem.Items.Count;
               string sqlCmd = null;
               for (int i = 0; i < count; i++)
               {
                    if (lvItem.Items[i].Selected == true)
                    {
                         sqlCmd = "DELETE FROM item WHERE item_id = "
                              + ((int)lvItem.Items[i].Tag).ToString();
                         break;
                    }
               }
               if (sqlCmd == null) return;
               SqlConnection conn = new SqlConnection(DatabaseInfo.GetConnectionInfo());
               SqlCommand sql = null;
               int rows;
               try
               {
                    conn.Open();
                    sql = new SqlCommand(sqlCmd, conn);
                    rows = sql.ExecuteNonQuery();
               }
               catch (SqlException ex)
               {
                    MessageBox.Show(this, ex.Message, "Error");
               }
               finally
               {
                    conn.Close();
                    FillListView();
               }
          }

          private void Form1_Load(object sender, EventArgs e)
          {
               lvItem.View = View.Details;
               lvItem.FullRowSelect = true;
               lvItem.GridLines = true;
               lvItem.HideSelection = false;
               //
               lvItem.Columns.Add("Name", 150, HorizontalAlignment.Left);
               ...
               btDelete.Enabled = false;
          }

          private void FillListView()
          {
               SqlConnection conn = new SqlConnection(DatabaseInfo.GetConnectionInfo());
               ...
          }
     }
}

Problem 6

Modify the DeleteItemS program to delete multiple items using C# in a desktop application. To find which items are selected in a List View control use:

ListView.SelectedIndexCollection indices = lvItem.SelectedIndices;

Problem 7

Convert the DeleteItem project to a Dual application using Wintempla. As toolbars are not supported in Web applications, you must replace the toolbar with a button. Remember to use the onclick event for the button in the Web application (in order to be able to check any event in a Web button, first you must uncheck the Submit property of the button). Note: after using Merge with Desktop Application, it may be necessary to add or edit the resulting HTML to get the desired layout in the web page.

DeleteItem.cpp

...
void DeleteItem::Window_Open(Win::Event& e)
{
     //________________________________________________ lvItem: Column Setup
     lvItem.Cols.Add(0, LVCFMT_LEFT, 200, L"Item name");
     lvItem.Cols.Add(1, LVCFMT_LEFT, 120, L"Model");
     lvItem.Cols.Add(2, LVCFMT_LEFT, 120, L"Brand");
     lvItem.Cols.Add(3, LVCFMT_LEFT, 120, L"Category");
     //
     DeleteItemDual::Window_Open(*this, NULL);
}

void DeleteItem::btDelete_Click(Win::Event& e)
{
     if (this->MessageBox(L"Are you sure you want to delete the selected item?", L"Delete Item",
          MB_YESNO | MB_ICONQUESTION) != IDYES) return;

     Win::HourGlassCursor hgc(true);
     DeleteItemDual::btDelete_Click(*this, NULL);
}

Index.cpp

void Index::Window_Open(Web::HttpConnector& h)
{
     //________________________________________________ lvItem: Column Setup
     lvItem.Cols.Add(LVCFMT_LEFT, 30, L"Item name");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Model");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Brand");
     lvItem.Cols.Add(LVCFMT_LEFT, 20, L"Category");
     lvItem.Height = 30;
     DeleteItemDual::Window_Open(*this, &h);
}

void Index::btDelete_onclick(Web::HttpConnector& h)
{
     DeleteItemDual::btDelete_Click(*this, &h);
}

DeleteItemDual.h

#pragma once //_____________________________________________ DeleteItemDual.h

class DeleteItemDual
{
public:
     DeleteItemDual()
     {
          Init();
     }
     ~DeleteItemDual()
     {
     }
     void FillListView(Sys::IWindow& window, Web::HttpConnector* h);
     ...
};

DeleteItemDual.cpp

...
void DeleteItemDual::Window_Open(Sys::IWindow& window, Web::HttpConnector* h)
{
     FillListView(window, h);
}

void DeleteItemDual::btDelete_Click(Sys::IWindow& window, Web::HttpConnector* h)
{
     //____________________________________________________________ 1. Get item_id
     LPARAM item_id;
     if (lvItemD.GetSelectedData(item_id) == false) return;
     //____________________________________________________________ 2. Create DELETE statement
     wstring cmd;
     Sys::Format(cmd, L"DELETE FROM item WHERE item_id=%d", item_id);
     //____________________________________________________________ 3. Execute DELETE
     Sql::SqlConnection conn;
     int rows = 0;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(window, CONNECTION_STRING);
          rows = conn.ExecuteNonQuery(cmd);
          if (rows!=1)
          {
               window.MessageBox(Sys::Convert::ToString(rows), L"Error - deleted rows", MB_OK | MB_ICONERROR);
          }
     }
     catch (Sql::SqlException e)
     {
          window.MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
     FillListView(window, h);
}

void DeleteItemDual::FillListView(Sys::IWindow& window, Web::HttpConnector* h)
{
     lvItemD.DeleteAllItems();
     Sql::SqlConnection conn;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(window, CONNECTION_STRING);
          conn.ExecuteSelect(L"SELECT item_id, item_descr, model, brand_descr, category_descr FROM vw_item", 100, lvItemD);
     }
     catch (Sql::SqlException e)
     {
          window.MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

Deleting

Deleting

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home